﻿using System;

using Community.CsharpSqlite.SQLiteClient;

using log4net;

using VSTalk.Logger;

namespace VSTalk.Engine.Core.Data.Migration
{
    public class Migration1 : IMigrationScript
    {
        private static readonly ILog _log = LoggerFactory.Create(typeof(Migration1));
        private SqliteConnection _connection;

        public void Apply(SqliteConnection connection)
        {
            _connection = connection;
            _log.Info("Start updating db");
            _connection.Open();
            try
            {
                CreateDiscoCache();
                SetupVersion();
            }
            catch (Exception e)
            {
                _log.Fatal("Unexpected error", e);
                throw;
            }
            finally
            {
                _connection.Close();
            }

        }

        private void SetupVersion()
        {
            string sql = "update version set num = 2";
            var command = new SqliteCommand(sql, _connection);
            command.ExecuteNonQuery();
        }

        private void CreateDiscoCache()
        {
            string sql = @"
CREATE TABLE caps_info (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
node varchar(2048) NULL,
version varchar(2048) NULL,
hash varchar(2048) NULL
);
CREATE TABLE feature (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
uri varchar(2048) UNIQUE NOT NULL
);
CREATE TABLE caps_to_feature (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
caps_info_id int NOT NULL,
feature_id int NOT NULL,
FOREIGN KEY (caps_info_id) REFERENCES caps_info(id),
FOREIGN KEY (feature_id) REFERENCES feature(id)
)";
            var command = new SqliteCommand(sql, _connection);
            command.ExecuteNonQuery();
        }
    }
}